package yiguan;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.Logger;

public class Company {
	private static Logger logger = Logger.getLogger("Waiter");

	public static JSONArray getAllCompany() {
		JSONArray sa = new JSONArray();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from laborcom");
			while(rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("id"));
				String[] ss = new String[4];
				ss[0] = rs.getString(1);
				ss[1] = rs.getString(2);
				ss[2] = rs.getString(3);
				ss[3] = rs.getString(4);
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static String buildCompanySelect() {
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select id,comname from laborcom");
			while(rs.next()) {
				sb.append("<option value='").append(rs.getString(1)).append("'>");
				sb.append(rs.getString(2)).append("</option>");
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sb.toString();
	}

	public static boolean companyAdd(String comid, String comname, String qualify, String district) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("insert into laborcom(id,comname,qualify,district) values(?,?,?,?)");
			ps.setString(1, comid);
			ps.setString(2, comname);
			ps.setString(3, qualify);
			ps.setString(4, district);
			int rownum = ps.executeUpdate();
			if (rownum == 1) {
				retval = true;
				logger.info(comname + " - " + comid + " added!");
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static boolean companyEdit(String comid, String comname, String qualify, String district) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("update laborcom set comname=?,qualify=?,district=? where id=?");
			ps.setString(1, comname);
			ps.setString(2, qualify);
			ps.setString(3, district);
			ps.setString(4, comid);
			int rownum = ps.executeUpdate();
			logger.info("update laborcom : " + rownum);
			if (rownum == 1) {
				retval = true;
				logger.info(comname + " - " + comid + " updated!");
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static int companyDelete(String comid) {
		int retval = 0;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from waiter where comid='" + comid + "'");
			rs.next();
			if (rs.getInt(1) > 0)
				return 2;
			retval = stmt.executeUpdate("delete from laborcom where id='" + comid + "'");
			logger.info(comid + " deleted!");
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
}